﻿ using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using DO = AdminCondominio.Dominio;

namespace AdminCondominio.Persistencia
{
    public class AreaComunDAO
    {
        private SqlConnection _cnn;
        //private string _CadenaCnn = @"Data Source=AFBV-PC;Initial Catalog=BdCondominio;User ID=sa";
        private string _CadenaCnn = @"Data Source=localhost;Initial Catalog=BdCondominio;Persist Security Info=True;User ID=sa;Password=";

        public AreaComunDAO() {
            _cnn = new SqlConnection(_CadenaCnn);
        }

        public int InserAreaComun(DO.AreaComun value)
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"INSERT INTO [area_comun] VALUES(@NuPersonas,@Area,@CoTiAreaComun,@TxUbicacion,@Estado)";
            _cmd.Parameters.Add("@NuPersonas", SqlDbType.Int).Value = value.NuPersonas;
            _cmd.Parameters.Add("@Area", SqlDbType.Decimal).Value = value.Area;
            _cmd.Parameters.Add("@CoTiAreaComun", SqlDbType.Int).Value = value.TipoAreaComun.CoTipoAreaComun;
            _cmd.Parameters.Add("@TxUbicacion", SqlDbType.VarChar, 50).Value = value.Ubicacion;
            _cmd.Parameters.Add("@Estado", SqlDbType.VarChar, 50).Value = value.Estado;
            _cmd.Connection.Open();
            int FilasAfec = _cmd.ExecuteNonQuery();
            _cnn.Close();
            _cmd.Dispose();
            return FilasAfec;
        }

        public DO.AreaComun OptenerAreaComun(DO.AreaComun value)
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"SELECT [CoAreaComun],[NuPersonas],[Area],AC.[CoTiAreaComun],[TxUbicacion],[Estado],DesAreaComun 
                                    FROM [area_comun] AC INNER JOIN 
                                         [tipo_area_comun] TAC ON TAC.CoTiAreaComun=AC.CoTiAreaComun 
                                WHERE [CoAreaComun]=" + value.CoAreaComun;
            _cmd.Connection.Open();
            SqlDataReader _dr = _cmd.ExecuteReader();
            DO.AreaComun _Item = null;
            if (_dr.Read())
            {
                _Item = new DO.AreaComun();
                _Item.TipoAreaComun = new DO.TipoAreaComun();
                _Item.CoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                _Item.NuPersonas = _dr.IsDBNull(1) ? 0 : _dr.GetInt32(1);
                _Item.Area = _dr.IsDBNull(2) ? 0 : _dr.GetDecimal(2);
                _Item.TipoAreaComun.CoTipoAreaComun   = _dr.IsDBNull(3) ? 0 : _dr.GetInt32(3);
                _Item.Ubicacion = _dr.IsDBNull(4) ? string.Empty : _dr.GetString(4);
                _Item.Estado= _dr.IsDBNull(5) ? string.Empty : _dr.GetString(5);
                _Item.TipoAreaComun.DesAreaComun = _dr.IsDBNull(6) ? string.Empty : _dr.GetString(6);
            }
            _cnn.Close();
            _cmd.Dispose();
            return _Item;
        }

        public List<DO.AreaComun> ListAreaComun()
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"SELECT [CoAreaComun],[NuPersonas],[Area],AC.[CoTiAreaComun],[TxUbicacion],[Estado],DesAreaComun 
                                    FROM [area_comun] AC INNER JOIN 
                                         [tipo_area_comun] TAC ON TAC.CoTiAreaComun=AC.CoTiAreaComun";
            _cmd.Connection.Open();
            SqlDataReader _dr = _cmd.ExecuteReader();
            List<DO.AreaComun> _LisAreaComun = new List<DO.AreaComun>();
            while (_dr.Read())
            {
                DO.AreaComun _Item = new DO.AreaComun();
                _Item.TipoAreaComun = new DO.TipoAreaComun();
                _Item.CoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                _Item.NuPersonas = _dr.IsDBNull(1) ? 0 : _dr.GetInt32(1);
                _Item.Area = _dr.IsDBNull(2) ? 0 : _dr.GetDecimal(2);
                _Item.TipoAreaComun.CoTipoAreaComun = _dr.IsDBNull(3) ? 0 : _dr.GetInt32(3);
                _Item.Ubicacion = _dr.IsDBNull(4) ? string.Empty : _dr.GetString(4);
                _Item.Estado = _dr.IsDBNull(5) ? string.Empty : _dr.GetString(5);
                _Item.TipoAreaComun.DesAreaComun = _dr.IsDBNull(6) ? string.Empty : _dr.GetString(6);
                _LisAreaComun.Add(_Item);  
            }
            _cnn.Close();
            _cmd.Dispose();
            return _LisAreaComun;
        }

        public List<DO.AreaComun> ListAreaComunxTipo(int CoTipoAreaComun)
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"SELECT [CoAreaComun],[NuPersonas],[Area],AC.[CoTiAreaComun],[TxUbicacion],[Estado],DesAreaComun 
                                    FROM [area_comun] AC INNER JOIN 
                                         [tipo_area_comun] TAC ON TAC.CoTiAreaComun=AC.CoTiAreaComun where AC.[CoTiAreaComun]=" + CoTipoAreaComun.ToString();
            _cmd.Connection.Open();
            SqlDataReader _dr = _cmd.ExecuteReader();
            List<DO.AreaComun> _LisAreaComun = new List<DO.AreaComun>();
            while (_dr.Read())
            {
                DO.AreaComun _Item = new DO.AreaComun();
                _Item.TipoAreaComun = new DO.TipoAreaComun();
                _Item.CoAreaComun = _dr.IsDBNull(0) ? 0 : _dr.GetInt32(0);
                _Item.NuPersonas = _dr.IsDBNull(1) ? 0 : _dr.GetInt32(1);
                _Item.Area = _dr.IsDBNull(2) ? 0 : _dr.GetDecimal(2);
                _Item.TipoAreaComun.CoTipoAreaComun = _dr.IsDBNull(3) ? 0 : _dr.GetInt32(3);
                _Item.Ubicacion = _dr.IsDBNull(4) ? string.Empty : _dr.GetString(4);
                _Item.Estado = _dr.IsDBNull(5) ? string.Empty : _dr.GetString(5);
                _Item.TipoAreaComun.DesAreaComun = _dr.IsDBNull(6) ? string.Empty : _dr.GetString(6);
                _LisAreaComun.Add(_Item);
            }
            _cnn.Close();
            _cmd.Dispose();
            return _LisAreaComun;
        }

        public int DeleteAreaComun(DO.AreaComun value)
        {
            SqlCommand _cmd = new SqlCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.Connection = _cnn;
            _cmd.CommandText = @"DELETE [area_comun] WHERE [CoAreaComun]=" + value.CoAreaComun;
            _cmd.Connection.Open();
            int _NunRefAfct = _cmd.ExecuteNonQuery();
            _cnn.Close();
            _cmd.Dispose();
            return _NunRefAfct;
        }

        public int UpdateAreaComun(DO.AreaComun value)
        {
            SqlCommand _cmd = new SqlCommand();
            try
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.Connection = _cnn;
                _cmd.CommandText = @"UPDATE [area_comun] SET [NuPersonas]=" + value.NuPersonas +
                                                ",[Area]=" + value.Area +
                                                ",[CoTiAreaComun]=" + value.TipoAreaComun.CoTipoAreaComun +
                                                ",[TxUbicacion]='" + value.Ubicacion +
                                                "',[Estado]='" + value.Estado +
                                                "' WHERE [CoAreaComun]=" + value.CoAreaComun;
                _cmd.Connection.Open();
                int _NunRefAfct = _cmd.ExecuteNonQuery();
                return _NunRefAfct;
            }
            catch (Exception)
            {
                throw;
            }
            finally {
                _cnn.Close();
                _cmd.Dispose();
            }
        }

    }
}